{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Helpdesk Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "-- \u001b[1mAttaching packages\u001b[22m --------------------------------------- tidyverse 1.3.0 --\n",
      "\n",
      "\u001b[32mv\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32mv\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.4\n",
      "\u001b[32mv\u001b[39m \u001b[34mtibble \u001b[39m 3.0.1     \u001b[32mv\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32mv\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32mv\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32mv\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32mv\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "-- \u001b[1mConflicts\u001b[22m ------------------------------------------ tidyverse_conflicts() --\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ·········\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "List the Company name and the number of calls for those companies with more than 18 calls.\n",
    "\n",
    "```\n",
    "+------------------+----+\n",
    "| Company_name     | cc |\n",
    "+------------------+----+\n",
    "| Gimmick Inc.     | 22 |\n",
    "| Hamming Services | 19 |\n",
    "| High and Co.     | 20 |\n",
    "+------------------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "shift <- dbReadTable(con, 'Shift')\n",
    "staff <- dbReadTable(con, 'Staff')\n",
    "issue <- dbReadTable(con, 'Issue')\n",
    "shift_type <- dbReadTable(con, 'Shift_type')\n",
    "level <- dbReadTable(con, 'Level')\n",
    "customer <- dbReadTable(con, 'Customer')\n",
    "caller <- dbReadTable(con, 'Caller')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 3 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>Company_name</th><th scope=col>cc</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Gimmick Inc.    </td><td>22</td></tr>\n",
       "\t<tr><td>Hamming Services</td><td>19</td></tr>\n",
       "\t<tr><td>High and Co.    </td><td>20</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 3 × 2\n",
       "\\begin{tabular}{ll}\n",
       " Company\\_name & cc\\\\\n",
       " <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t Gimmick Inc.     & 22\\\\\n",
       "\t Hamming Services & 19\\\\\n",
       "\t High and Co.     & 20\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 3 × 2\n",
       "\n",
       "| Company_name &lt;chr&gt; | cc &lt;int&gt; |\n",
       "|---|---|\n",
       "| Gimmick Inc.     | 22 |\n",
       "| Hamming Services | 19 |\n",
       "| High and Co.     | 20 |\n",
       "\n"
      ],
      "text/plain": [
       "  Company_name     cc\n",
       "1 Gimmick Inc.     22\n",
       "2 Hamming Services 19\n",
       "3 High and Co.     20"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "issue %>%\n",
    "    inner_join(caller, by=c(Caller_id=\"Caller_id\")) %>%\n",
    "    inner_join(customer, by=c(Company_ref=\"Company_ref\")) %>%\n",
    "    group_by(Company_name) %>%\n",
    "    summarise(cc=n()) %>%\n",
    "    filter(cc>18) %>%\n",
    "    arrange(Company_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "Find the callers who have never made a call. Show first name and last name\n",
    "\n",
    "```\n",
    "+------------+-----------+\n",
    "| first_name | last_name |\n",
    "+------------+-----------+\n",
    "| David      | Jackson   |\n",
    "| Ethan      | Phillips  |\n",
    "+------------+-----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 2 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>First_name</th><th scope=col>Last_name</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>David</td><td>Jackson </td></tr>\n",
       "\t<tr><td>Ethan</td><td>Phillips</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 2 × 2\n",
       "\\begin{tabular}{ll}\n",
       " First\\_name & Last\\_name\\\\\n",
       " <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t David & Jackson \\\\\n",
       "\t Ethan & Phillips\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 2 × 2\n",
       "\n",
       "| First_name &lt;chr&gt; | Last_name &lt;chr&gt; |\n",
       "|---|---|\n",
       "| David | Jackson  |\n",
       "| Ethan | Phillips |\n",
       "\n"
      ],
      "text/plain": [
       "  First_name Last_name\n",
       "1 David      Jackson  \n",
       "2 Ethan      Phillips "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "caller %>%\n",
    "    left_join(issue, by=c(Caller_id=\"Caller_id\")) %>%\n",
    "    group_by(Caller_id, First_name, Last_name) %>%\n",
    "    tally(wt=!is.na(Call_ref)) %>%\n",
    "    ungroup %>%\n",
    "    filter(n<1) %>%\n",
    "    select(First_name, Last_name) %>%\n",
    "    arrange(First_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "For each customer show: Company name, contact name, number of calls where the number of calls is fewer than 5\n",
    "\n",
    "```\n",
    "+--------------------+------------+-----------+----+\n",
    "| Company_name       | first_name | last_name | nc |\n",
    "+--------------------+------------+-----------+----+\n",
    "| Pitiable Shipping  | Ethan      | McConnell |  4 |\n",
    "| Rajab Group        | Emily      | Cooper    |  4 |\n",
    "| Somebody Logistics | Ethan      | Phillips  |  2 |\n",
    "+--------------------+------------+-----------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 3 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>Company_name</th><th scope=col>First_name.y</th><th scope=col>Last_name.y</th><th scope=col>nc</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Pitiable Shipping </td><td>Ethan</td><td>McConnell</td><td>4</td></tr>\n",
       "\t<tr><td>Rajab Group       </td><td>Emily</td><td>Cooper   </td><td>4</td></tr>\n",
       "\t<tr><td>Somebody Logistics</td><td>Ethan</td><td>Phillips </td><td>2</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 3 × 4\n",
       "\\begin{tabular}{llll}\n",
       " Company\\_name & First\\_name.y & Last\\_name.y & nc\\\\\n",
       " <chr> & <chr> & <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t Pitiable Shipping  & Ethan & McConnell & 4\\\\\n",
       "\t Rajab Group        & Emily & Cooper    & 4\\\\\n",
       "\t Somebody Logistics & Ethan & Phillips  & 2\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 3 × 4\n",
       "\n",
       "| Company_name &lt;chr&gt; | First_name.y &lt;chr&gt; | Last_name.y &lt;chr&gt; | nc &lt;int&gt; |\n",
       "|---|---|---|---|\n",
       "| Pitiable Shipping  | Ethan | McConnell | 4 |\n",
       "| Rajab Group        | Emily | Cooper    | 4 |\n",
       "| Somebody Logistics | Ethan | Phillips  | 2 |\n",
       "\n"
      ],
      "text/plain": [
       "  Company_name       First_name.y Last_name.y nc\n",
       "1 Pitiable Shipping  Ethan        McConnell   4 \n",
       "2 Rajab Group        Emily        Cooper      4 \n",
       "3 Somebody Logistics Ethan        Phillips    2 "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "issue %>%\n",
    "    inner_join(caller, by=c(Caller_id=\"Caller_id\")) %>%\n",
    "    inner_join(customer, by=c(Company_ref=\"Company_ref\")) %>%\n",
    "    inner_join(caller, by=c(Contact_id=\"Caller_id\")) %>%\n",
    "    group_by(Company_name, Company_ref.x, First_name.y, Last_name.y) %>%\n",
    "    tally(wt=!is.na(Call_ref), name=\"nc\") %>%\n",
    "    ungroup %>%\n",
    "    filter(nc<5) %>%\n",
    "    arrange(Company_name) %>%\n",
    "    select(Company_name, First_name.y, Last_name.y, nc)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "For each shift show the number of staff assigned. Beware that some roles may be NULL and that the same person might have been assigned to multiple roles (The roles are 'Manager', 'Operator', 'Engineer1', 'Engineer2').\n",
    "\n",
    "```\n",
    "+------------+------------+----+\n",
    "| Shift_date | Shift_type | cw |\n",
    "+------------+------------+----+\n",
    "| 2017-08-12 | Early      |  4 |\n",
    "| 2017-08-12 | Late       |  4 |\n",
    "| 2017-08-13 | Early      |  3 |\n",
    "| 2017-08-13 | Late       |  2 |\n",
    "| 2017-08-14 | Early      |  4 |\n",
    "| 2017-08-14 | Late       |  4 |\n",
    "| 2017-08-15 | Early      |  4 |\n",
    "| 2017-08-15 | Late       |  4 |\n",
    "| 2017-08-16 | Early      |  4 |\n",
    "| 2017-08-16 | Late       |  4 |\n",
    "+------------+------------+----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "ERROR",
     "evalue": "Error in parse(text = x, srcfile = src): <text>:1:8: unexpected symbol\n1: SELECT agg.\n           ^\n",
     "output_type": "error",
     "traceback": [
      "Error in parse(text = x, srcfile = src): <text>:1:8: unexpected symbol\n1: SELECT agg.\n           ^\nTraceback:\n"
     ]
    }
   ],
   "source": [
    "SELECT agg.\"Shift_date\", agg.\"Shift_type\", COUNT(agg.p) cw FROM\n",
    "(SELECT DISTINCT a.\"Shift_date\", a.\"Shift_type\", a.p FROM\n",
    "  (SELECT \"Shift_date\", \"Shift_type\", \"Manager\" p FROM \"Shift\" \n",
    "    UNION SELECT \"Shift_date\", \"Shift_type\", \"Operator\" p FROM \"Shift\" \n",
    "    UNION SELECT \"Shift_date\", \"Shift_type\", \"Engineer1\" p FROM \"Shift\" \n",
    "    UNION SELECT \"Shift_date\", \"Shift_type\", \"Engineer2\" p FROM \"Shift\" \n",
    "  ) a\n",
    ") AS agg\n",
    "GROUP BY agg.\"Shift_date\", agg.\"Shift_type\"\n",
    "ORDER BY agg.\"Shift_date\", agg.\"Shift_type\";\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "shift %>% \n",
    "    pivot_longer(Manager:Engineer2, names_to=\"role\", values_to=\"who\") %>%\n",
    "    distinct(Shift_date, Shift_type, who) %>%\n",
    "    group_by(Shift_date, Shift_type) %>%\n",
    "    tally(!is.na(who), name='cw')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "Caller 'Harry' claims that the operator who took his most recent call was abusive and insulting. Find out who took the call (full name) and when.\n",
    "\n",
    "```\n",
    "+------------+-----------+---------------------+\n",
    "| first_name | last_name | call_date           |\n",
    "+------------+-----------+---------------------+\n",
    "| Emily      | Best      | 2017-08-16 10:25:00 |\n",
    "+------------+-----------+---------------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "caller %>%\n",
    "    inner_join(issue, by=c(Caller_id=\"Caller_id\")) %>%\n",
    "    inner_join(staff, by=c(Taken_by=\"Staff_code\")) %>%\n",
    "    filter(First_name.x=='Harry') %>%\n",
    "    arrange(desc(Call_date)) %>%\n",
    "    select(First_name.y, Last_name.y, Call_date) %>%\n",
    "    slice(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
